-- @owner: ningyali
-- @date: 2024-08-12
-- @testpoint: count(column) column列为自增列,查询其他列中没有索引-走全表扫描

--step1:创建测试表; expect: 执行成功
drop table if exists t_ustore_count_optim_0009;
create table t_ustore_count_optim_0009(
	c_id int  primary key, c_int  integer , c_bool bool,
	c_real real ,
	c_char char(300),c_varchar2 varchar2(4000) default lpad('ee2t2xnbzzkih',80,'etskbfw@p%my8tesjbemus6gnv3'),
	c_timestamp timestamp not null
	) with (storage_type=ustore);

--step2:创建序列; expect: 执行成功
drop sequence if exists seq_ustore_count_optim_0009;
create sequence seq_ustore_count_optim_0009 increment by 1 start with 100000;

--step3:插入数据; expect: 执行成功
begin
	for i in 1..1000 loop
	insert into t_ustore_count_optim_0009(c_id,c_int,c_bool,c_real,c_char,c_timestamp)  values(seq_ustore_count_optim_0009.nextval,i+3,'true',i%16+4.9690546872 ,lpad('ee2t2xnbzzkih',50,'et'),to_timestamp('19-aug-2004 21:38:09.123000','dd-mon-yyyy hh24:mi:ss.ff') );
    end loop;
end;
/

--step4:count(column) column列为自增列,查询其他列中没有索引-走全表扫描; expect: 执行成功
select c_real, count(c_id) from t_ustore_count_optim_0009 group by c_real order by c_real limit 5;
explain analyze select c_real, count(c_id) from t_ustore_count_optim_0009 group by c_real order by c_real  limit 5;

--step5:清理环境; expect: 执行成功
drop table if exists t_ustore_count_optim_0009;
drop sequence seq_ustore_count_optim_0009;